﻿using System;
using System.Collections.Generic;
using System.IO;
using System.IO.IsolatedStorage;
using System.Linq;
using System.Net;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Navigation;
using System.Xml;
using System.Xml.Serialization;
using Microsoft.Phone.Controls;
using Microsoft.Phone.Shell;
using TestMatrix.BLController;
using TestMatrix.Helper;
using TestMatrix.Model;
using Visifire.Charts;
using System.Windows.Resources;
using System.Text;
using System.Xml.Linq;
using Windows.Storage;
using System.Threading;
using System.Threading.Tasks;
using Telerik.Windows.Controls;
using Microsoft.WindowsAzure.MobileServices;
using System.Diagnostics;
using Newtonsoft.Json;
using SQLite;
using TestMatrix.DataModel;

namespace TestMatrix.Phone
{
    public partial class MatrixSummaryPage : PhoneApplicationPage
    {
        ApplicationBarController appbar;
        FilterSettingController filterSettingController;
        private IsolatedStorageSettings userSettings = IsolatedStorageSettings.ApplicationSettings;

        private IMobileServiceTable<ETLHistoryAzure> etlHistoryTable = App.MobileService.GetTable<ETLHistoryAzure>();

        private IMobileServiceTable<BugListAzure> bugListAzureTable = App.MobileService.GetTable<BugListAzure>();
        //private MobileServiceCollection<BugListAzure, BugListAzure> bugItems;

        private IMobileServiceTable<NumOfCreatedTCAzure> NumOfCreatedTCAzureTable = App.MobileService.GetTable<NumOfCreatedTCAzure>();
        
        private IMobileServiceTable<TestAutomationAzure> TestAutomationAzureTable = App.MobileService.GetTable<TestAutomationAzure>();
        
        private IMobileServiceTable<NumOfExecutedTCAzure> NumOfExecutedTCAzureTable = App.MobileService.GetTable<NumOfExecutedTCAzure>();
        
        private IMobileServiceTable<ReleaseInfoAzure> ReleaseInfoAzureTable = App.MobileService.GetTable<ReleaseInfoAzure>();

        private IMobileServiceTable<TestCaseInCIAzure> TestCaseInCIAzureTable = App.MobileService.GetTable<TestCaseInCIAzure>();

        private IMobileServiceTable<GlobalTestCasePassRateAzure> GlobalTestCasePassRateAzureAzureTable = App.MobileService.GetTable<GlobalTestCasePassRateAzure>();

        private IMobileServiceTable<GlobalTestCaseAutomationAzure> GlobalTestCaseAutomationAzureTable = App.MobileService.GetTable<GlobalTestCaseAutomationAzure>();

        private IMobileServiceTable<GlobalDREAzure> GlobalDREAzureAzureTable = App.MobileService.GetTable<GlobalDREAzure>();

        public MatrixSummaryPage()
        {
            InitializeComponent();

            
            InitializeControlEvents();
            InitializeControls();
            InitializeDataFilter();
            SummaryPivot.Title = filterSettingController.DisplayRefreshDuration();
            InitializeUserInfo();
            //InitializeDBFile();

            //DataCheck();
           
        }

        void DataCheck()
        {

            string sql = "select count(*) as Count from tblBugList";

            List<tblBugList> listDatas = ChartController.Query(sql);

            if (listDatas[0].Count==0)
            {
                SummaryPivot.SelectedIndex = 0;

                this.panelRefresh.Visibility = System.Windows.Visibility.Visible;
                this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Collapsed;

                this.appbar.ShowButtons("cancel");
            }
        }

        void InitializeDBFile()
        {
            try
            {
                //ConstantDefinition.DB_PATH
                //if (!string.IsNullOrEmpty(ConstantDefinition.GetDBPath()))
                if (!string.IsNullOrEmpty(ConstantDefinition.DB_PATH))
                {
                    InitializeFirstChart();
                }
                else
                {
                    SummaryPivot.SelectedIndex = 0;

                    this.panelRefresh.Visibility = System.Windows.Visibility.Visible;
                    this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                    this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                    this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Collapsed;

                    this.appbar.ShowButtons("cancel");
                }
            }
            catch (FileNotFoundException)
            {
              
            }
        }

        void InitializeUserInfo()
        {
            try
            {
                this.EmailTextBox.Text = (string)userSettings[ConstantDefinition.USER_ALIAS];
            }
            catch (System.Collections.Generic.KeyNotFoundException)
            {
                this.EmailTextBox.Text = @"Domain\Alias";
            }

            try
            {
                this.AccountPasswordBox.Password = (string)userSettings[ConstantDefinition.USER_PASSWORD];
            }
            catch (System.Collections.Generic.KeyNotFoundException)
            {
                this.AccountPasswordBox.Password = "password";
            }

        }

        void InitializeFirstChart()
        {
            this.EnvironmentBySeverityChart.Series.Clear();
            ShowEnvironmentBySeverityStackedBar(RenderAs.StackedBar);//EnvironmentBySeverity
        }

        private void RefreshSetting()
        {
            string dateFilterType = ChartController.ReadSettingFromXml();
            filterSettingController = new FilterSettingController(dateFilterType);
            SummaryPivot.Title = filterSettingController.DisplayRefreshDuration();
        }

        void InitializeDataFilter()
        {
            try
            {
                string dateFilterType = ChartController.ReadSettingFromXml();
                filterSettingController = new FilterSettingController(dateFilterType);

                if (this.radButtonAll.Content.Equals(dateFilterType))
                {
                    this.radButtonAll.IsChecked = true;
                }
                else if (this.radButtonLastMonth.Content.Equals(dateFilterType))
                {
                    this.radButtonLastMonth.IsChecked = true;
                }
                else if (this.radButtonLastHalfYears.Content.Equals(dateFilterType))
                {
                    this.radButtonLastHalfYears.IsChecked = true;
                }
                else if (this.radButtonLastYears.Content.Equals(dateFilterType))
                {
                    this.radButtonLastYears.IsChecked = true;
                }
            }
            catch
            {

            }
        }

        void InitializeControls()
        {
            this.appbar = new ApplicationBarController(this.ApplicationBar);
            //prepare all icon buttons and menu Items
            this.appbar.AddNewButton("project menu", "/icons/appbar.favs.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("setting", "/icons/appbar.edit.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("refresh", "/icons/appbar.refresh.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("save", "/icons/appbar.save.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("cancel", "/icons/appbar.close.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("back", "/icons/appbar.back.rest.png", true, ApplicationBarIconButton_Click);
            this.appbar.AddNewButton("stop", "/icons/appbar.transport.pause.rest.png", true, ApplicationBarIconButton_Click);
        }

        private void ApplicationBarIconButton_Click(object sender, EventArgs e)
        {
            ApplicationBarIconButton btn = (ApplicationBarIconButton)sender;
            string text = btn.Text.ToLower();

            if (text == "project menu")
            {
                SummaryPivot.SelectedIndex = 0;

                this.panelPrjList.Visibility = System.Windows.Visibility.Visible;
                this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                this.panelRefresh.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Collapsed;

                ShowProjectList();
                this.appbar.ShowButtons("cancel");
            }
            else if (text == "setting")
            {
                SummaryPivot.SelectedIndex = 0;
                this.panelSetting.Visibility = System.Windows.Visibility.Visible;
                this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                this.panelRefresh.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Collapsed;

                this.appbar.ShowButtons("save", "cancel");
            }
            else if (text == "refresh")
            {
                SummaryPivot.SelectedIndex = 0;

                this.panelRefresh.Visibility = System.Windows.Visibility.Visible;
                this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Collapsed;

                this.appbar.ShowButtons("cancel");
            }
            else if (text == "cancel")
            {
                this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                this.panelRefresh.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Visible;

                WriteSettingToXML();
                RefreshSetting();
                InitializeFirstChart();
                this.appbar.ShowButtons("project menu", "setting", "refresh");
            }
            else if (text == "save")
            {
                SummaryPivot.SelectedIndex = 0;
                this.panelPrjList.Visibility = System.Windows.Visibility.Collapsed;
                this.panelSetting.Visibility = System.Windows.Visibility.Collapsed;
                this.panelRefresh.Visibility = System.Windows.Visibility.Collapsed;
                this.EnvironmentBySeverityChart.Visibility = System.Windows.Visibility.Visible;

                WriteSettingToXML();
                RefreshSetting();
                InitializeFirstChart();
                this.appbar.ShowButtons("project menu", "setting", "refresh");
            }
        }

        void WriteSettingToXML()
        {
            string strOutPut = "";

            if (this.radButtonAll.IsChecked == true)
            {
                strOutPut = this.radButtonAll.Content.ToString();
            }
            else if (this.radButtonLastMonth.IsChecked == true)
            {
                strOutPut = this.radButtonLastMonth.Content.ToString();
            }
            else if (this.radButtonLastHalfYears.IsChecked == true)
            {
                strOutPut = this.radButtonLastHalfYears.Content.ToString();
            }
            else if (this.radButtonLastYears.IsChecked == true)
            {
                strOutPut = this.radButtonLastYears.Content.ToString();
            }

            List<PhoneSetting> data = new List<PhoneSetting>();
            data.Add(new PhoneSetting() { DateFilterType = strOutPut });
            // Write to the Isolated Storage
            ChartController.WriteSettingToXml(data);
            #region
            //List<PhoneSetting> data = new List<PhoneSetting>();
            //data.Add(new PhoneSetting() { DateFilterType = strOutPut });

            //// Write to the Isolated Storage
            //XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
            //xmlWriterSettings.Indent = true;

            //using (IsolatedStorageFile myIsolatedStorage = IsolatedStorageFile.GetUserStoreForApplication())
            //{
            //    using (IsolatedStorageFileStream stream = myIsolatedStorage.OpenFile("Setting.xml", FileMode.Create))
            //    {
            //        XmlSerializer serializer = new XmlSerializer(typeof(List<PhoneSetting>));
            //        using (XmlWriter xmlWriter = XmlWriter.Create(stream, xmlWriterSettings))
            //        {
            //            serializer.Serialize(xmlWriter, data);
            //        }
            //    }
            //}
            #endregion
        }

        void InitializeControlEvents()
        {
            SummaryPivot.SelectionChanged += new SelectionChangedEventHandler(SummaryPivot_SelectionChanged);
        }

        void SummaryPivot_SelectionChanged(object sender, SelectionChangedEventArgs e)
        {

            int i = SummaryPivot.SelectedIndex;

            switch (i)
            {
                case 0:

                    this.EnvironmentBySeverityChart.Series.Clear();
                    ShowEnvironmentBySeverityStackedBar(RenderAs.StackedBar);//EnvironmentBySeverity
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");
                    DataCheck();
                    break;
                case 1:

                    this.ProjectNameByStateChart.Series.Clear();
                    ShowProjectNameByStateStackedBar(RenderAs.StackedBar);//ProjectNameByState
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 2:

                    this.BugTypeByEnvironmentChart.Series.Clear();
                    ShowBugTypeByEnvironmentStackedBar(RenderAs.StackedBar);//BugTypeByEnvironment
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 3:

                    this.ProjectNameBySeverityChart.Series.Clear();
                    ShowProjectNameBySeverityStackedBar(RenderAs.StackedBar);//ProjectNameBySeverity
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 4:

                    this.StateBySeverityChart.Series.Clear();
                    ShowStateBySeverityStackedBar(RenderAs.StackedBar);//StateBySeverity
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 5:

                    this.ProjectNameChart.Series.Clear();
                    displayProjectNameFunnelChart(RenderAs.Bar); //ProjectName
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 6:

                    this.CreatedDateSplineChart.Series.Clear();
                    displayCreatedDateSplineChart(RenderAs.Spline);//CreatedDate
                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;
                case 7:

                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");

                    break;

                case 8:

                    this.appbar.ShowAppBar(true);
                    this.appbar.ShowButtons("project menu", "setting", "refresh");
                    //this.appbar.ShowMenuItem("Message");

                    break;
            }
        }

        private void ShowProjectList()
        {
            string sql = "select distinct ProjectFriendName from tblBugList order by ProjectFriendName";

            List<tblBugList> listDatas = ChartController.Query(sql);

            this.panelPrjList.Children.Clear();

            foreach (var t in listDatas)
            {
                Button btn = new Button();
                btn.HorizontalAlignment = System.Windows.HorizontalAlignment.Center;
                btn.Width = 400;
                btn.Click += ShowProjectList_Click;
                btn.Content = t.ProjectFriendName;
                this.panelPrjList.Children.Add(btn);
            }

        }

        void ShowProjectList_Click(object sender, RoutedEventArgs e)
        {
            Button btn = (Button)sender;
            string name = btn.Content.ToString();
            //name = System.Net.HttpUtility.HtmlEncode(name);
            name = System.Net.HttpUtility.UrlEncode(name);
            NavigationService.Navigate(new Uri(@"/MatrixDetailsPage.xaml?PrjName=" + name, UriKind.Relative));
        }

        /// <summary>
        /// This is to show EnvironmentBySeverity
        /// </summary>
        /// <param name="ra"></param>
        private void ShowEnvironmentBySeverityStackedBar(RenderAs ra)
        {

            string strSeries = "select distinct Severity from tblBugList order by Severity";

            List<tblBugList> listSeries = ChartController.Query(strSeries);

            DataSeries dataSeries;

            foreach (tblBugList tblBugs in listSeries)
            {
                dataSeries = new DataSeries();
                dataSeries.RenderAs = ra;
                //dataSeries.LabelEnabled = true;

                dataSeries.ShowInLegend = true;
                //dataSeries.IncludePercentageInLegend = true;

                dataSeries.LabelText = "#YValue";
                dataSeries.LegendText = tblBugs.Severity.ToString();

                string strDataPoint = "select a.FoundInEnvironment FoundInEnvironment,case when (b.FoundInEnvironment is null) then 0 else b.Count end Count from (select distinct FoundInEnvironment from tblBugList) a left outer join (select FoundInEnvironment,COUNT(1) as Count from tblBugList where 1=1 and Severity=? group by FoundInEnvironment) b on a.FoundInEnvironment=b.FoundInEnvironment order by a.FoundInEnvironment ";
                List<tblBugList> listDataPoints = ChartController.Query(filterSettingController.AppendDateFilter(strDataPoint), tblBugs.Severity.ToString());

                DataPoint dp;
                for (int i = 0; i < listDataPoints.Count(); i++)
                {
                    dp = new DataPoint();
                    dp.AxisXLabel = listDataPoints[i].FoundInEnvironment;
                    dp.YValue = listDataPoints[i].Count;
                    dp.Exploded = true;
                    dataSeries.DataPoints.Add(dp);
                }
                EnvironmentBySeverityChart.Series.Add(dataSeries);
            }
        }

        /// <summary>
        /// This is to show ProjectNameByStateChart
        /// </summary>
        /// <param name="ra"></param>
        private void ShowProjectNameByStateStackedBar(RenderAs ra)
        {

            string strSeries = "select distinct State from tblBugList order by State";

            List<tblBugList> listSeries = ChartController.Query(strSeries);

            DataSeries dataSeries;

            foreach (tblBugList tblBugs in listSeries)
            {
                dataSeries = new DataSeries();
                dataSeries.RenderAs = ra;
                //dataSeries.LabelEnabled = true;

                dataSeries.ShowInLegend = true;
                //dataSeries.IncludePercentageInLegend = true;

                dataSeries.LabelText = "#YValue";
                dataSeries.LegendText = tblBugs.State.ToString();

                string strDataPoint = "select a.ProjectFriendName ProjectFriendName,case when (b.ProjectFriendName is null) then 0 else b.Count end Count from (select distinct ProjectFriendName from tblBugList) a left outer join (select ProjectFriendName,COUNT(1) as Count from tblBugList where 1=1 and State=? group by ProjectFriendName) b on a.ProjectFriendName=b.ProjectFriendName order by a.ProjectFriendName ";
                List<tblBugList> listDataPoints = ChartController.Query(filterSettingController.AppendDateFilter(strDataPoint), tblBugs.State.ToString());

                DataPoint dp;
                for (int i = 0; i < listDataPoints.Count(); i++)
                {
                    dp = new DataPoint();
                    dp.AxisXLabel = listDataPoints[i].ProjectFriendName;
                    dp.YValue = listDataPoints[i].Count;
                    dp.Exploded = true;
                    dataSeries.DataPoints.Add(dp);
                }
                ProjectNameByStateChart.Series.Add(dataSeries);
            }
        }

        /// <summary>
        /// This is to show BugTypeByEnvironmentChart
        /// </summary>
        /// <param name="ra"></param>
        private void ShowBugTypeByEnvironmentStackedBar(RenderAs ra)
        {

            string strSeries = "select distinct FoundInEnvironment from tblBugList order by FoundInEnvironment";

            List<tblBugList> listSeries = ChartController.Query(strSeries);

            DataSeries dataSeries;

            foreach (tblBugList tblBugs in listSeries)
            {
                dataSeries = new DataSeries();
                dataSeries.RenderAs = ra;
                //dataSeries.LabelEnabled = true;

                dataSeries.ShowInLegend = true;
                //dataSeries.IncludePercentageInLegend = true;

                dataSeries.LabelText = "#YValue";
                dataSeries.LegendText = tblBugs.FoundInEnvironment.ToString();

                string strDataPoint = "select a.BugType BugType,case when (b.BugType is null) then 0 else b.Count end Count from (select distinct BugType from tblBugList) a left outer join (select BugType,COUNT(1) as Count from tblBugList where 1=1 and FoundInEnvironment=? group by BugType) b on a.BugType=b.BugType order by a.BugType ";
                List<tblBugList> listDataPoints = ChartController.Query(filterSettingController.AppendDateFilter(strDataPoint), tblBugs.FoundInEnvironment.ToString());

                DataPoint dp;
                for (int i = 0; i < listDataPoints.Count(); i++)
                {
                    dp = new DataPoint();
                    dp.AxisXLabel = listDataPoints[i].BugType;
                    dp.YValue = listDataPoints[i].Count;
                    dp.Exploded = true;
                    dataSeries.DataPoints.Add(dp);
                }
                BugTypeByEnvironmentChart.Series.Add(dataSeries);
            }
        }

        /// <summary>
        /// This is to show ProjectNameBySeverityChart
        /// </summary>
        /// <param name="ra"></param>
        private void ShowProjectNameBySeverityStackedBar(RenderAs ra)
        {

            string strSeries = "select distinct Severity from tblBugList order by Severity";

            List<tblBugList> listSeries = ChartController.Query(strSeries);

            DataSeries dataSeries;

            foreach (tblBugList tblBugs in listSeries)
            {
                dataSeries = new DataSeries();
                dataSeries.RenderAs = ra;
                //dataSeries.LabelEnabled = true;

                dataSeries.ShowInLegend = true;
                //dataSeries.IncludePercentageInLegend = true;

                dataSeries.LabelText = "#YValue";
                dataSeries.LegendText = tblBugs.Severity.ToString();

                string strDataPoint = "select a.ProjectFriendName ProjectFriendName,case when (b.ProjectFriendName is null) then 0 else b.Count end Count from (select distinct ProjectFriendName from tblBugList) a left outer join (select ProjectFriendName,COUNT(1) as Count from tblBugList where 1=1 and Severity=? group by ProjectFriendName) b on a.ProjectFriendName=b.ProjectFriendName order by a.ProjectFriendName ";
                List<tblBugList> listDataPoints = ChartController.Query(filterSettingController.AppendDateFilter(strDataPoint), tblBugs.Severity.ToString());

                DataPoint dp;
                for (int i = 0; i < listDataPoints.Count(); i++)
                {
                    dp = new DataPoint();
                    dp.AxisXLabel = listDataPoints[i].ProjectFriendName;
                    dp.YValue = listDataPoints[i].Count;
                    dp.Exploded = true;
                    dataSeries.DataPoints.Add(dp);
                }
                ProjectNameBySeverityChart.Series.Add(dataSeries);
            }
        }

        /// <summary>
        /// This is to show StateBySeverity
        /// </summary>
        /// <param name="ra"></param>
        private void ShowStateBySeverityStackedBar(RenderAs ra)
        {

            string strSeries = "select distinct Severity from tblBugList order by Severity";

            List<tblBugList> listSeries = ChartController.Query(strSeries);

            DataSeries dataSeries;

            foreach (tblBugList tblBugs in listSeries)
            {
                dataSeries = new DataSeries();
                dataSeries.RenderAs = ra;
                //dataSeries.LabelEnabled = true;

                dataSeries.ShowInLegend = true;
                //dataSeries.IncludePercentageInLegend = true;

                dataSeries.LabelText = "#YValue";
                dataSeries.LegendText = tblBugs.Severity.ToString();

                string strDataPoint = "select a.State State,case when (b.State is null) then 0 else b.Count end Count from (select distinct State from tblBugList) a left outer join (select State,COUNT(1) as Count from tblBugList where 1=1 and Severity=? group by State) b on a.State=b.State order by a.State ";
                List<tblBugList> listDataPoints = ChartController.Query(filterSettingController.AppendDateFilter(strDataPoint), tblBugs.Severity.ToString());

                DataPoint dp;
                for (int i = 0; i < listDataPoints.Count(); i++)
                {
                    dp = new DataPoint();
                    dp.AxisXLabel = listDataPoints[i].State;
                    dp.YValue = listDataPoints[i].Count;
                    dp.Exploded = true;
                    dataSeries.DataPoints.Add(dp);
                }
                StateBySeverityChart.Series.Add(dataSeries);
            }
        }

        /// <summary>
        /// This is to show Funnel Bug ProjectName
        /// </summary>
        /// <param name="ra"></param>
        /// <param name="str"></param>
        private void displayProjectNameFunnelChart(RenderAs ra)
        {

            string sql = "select ProjectFriendName,count(*) as Count from tblBugList where 1=1 group by ProjectFriendName order by Count desc";
            List<tblBugList> listDatas = ChartController.Query(filterSettingController.AppendDateFilter(sql));

            DataSeries dataSeries = new DataSeries();
            dataSeries.RenderAs = ra;
            dataSeries.LabelEnabled = true;
            DataPoint dp;
            for (int i = 0; i < listDatas.Count(); i++)
            {
                dp = new DataPoint();
                dp.AxisXLabel = listDatas[i].ProjectFriendName;
                dp.YValue = listDatas[i].Count;
                dataSeries.DataPoints.Add(dp);
            }

            ProjectNameChart.View3D = true;
            ProjectNameChart.Series.Add(dataSeries);
        }

        private void displayCreatedDateSplineChart(RenderAs ra)
        {

            //string sql = "select strftime('%Y-%m-%d',datetime(CreatedDate,'localtime')) as CreatedDate,count(*) as Count from tblBugList group by strftime('%Y-%m-%d',datetime(CreatedDate,'localtime')) order by Count desc";

            string sql = "select BugType,COUNT(*) as Count from tblBugList where 1=1 group by BugType order by Count desc";

            List<tblBugList> listDatas = ChartController.Query(filterSettingController.AppendDateFilter(sql));

            DataSeries dataSeries = new DataSeries();
            dataSeries.RenderAs = ra;
            dataSeries.ShadowEnabled = false;

            DataPoint dp;
            for (int i = 0; i < listDatas.Count(); i++)
            {
                dp = new DataPoint();
                //dp.AxisXLabel = listDatas[i].CreatedDate.ToShortDateString();
                dp.AxisXLabel = listDatas[i].BugType;
                dp.YValue = listDatas[i].Count;
                dataSeries.DataPoints.Add(dp);
            }

            //BugTypeChart.View3D = true;
            CreatedDateSplineChart.Series.Add(dataSeries);
        }
        private async void ExtractDataFromAzure_used()
        {
            this.OutputTextBox.Text = "";
            Stopwatch sw = new Stopwatch();

            sw.Start();
            this.OutputTextBox.Text = "----Starting Extract Data, please wait...---" + Environment.NewLine + OutputTextBox.Text;

            #region refresh BugList from Azure

            //truncate table tblBuglist
            ChartController.DeleteAllBugs();

            try
            {
                int takeCount = 900;
                int skipCount = 0;

                while (1 == 1)
                {
                    IMobileServiceTableQuery<BugListAzure> query = bugListAzureTable.Skip(skipCount).Take(takeCount);
                    List<BugListAzure> lstTmp = await query.ToListAsync();

                    List<tblBugList> lstTrg = new List<tblBugList>();
                    foreach (BugListAzure item in lstTmp)
                    {
                        tblBugList bug = new tblBugList();
                        bug.ID = item.Id;
                        bug.ItemID = item.ItemID;
                        bug.ProjectName = item.ProjectName;
                        bug.AssignedTo = item.AssignedTo;
                        bug.State = item.State;
                        bug.Severity = item.Severity;
                        bug.FoundInEnvironment = item.FoundInEnvironment;
                        bug.CreatedDate = item.CreatedDate;
                        bug.ResolvedDate = item.ResolvedDate;
                        bug.ClosedDate = item.ClosedDate;
                        bug.ProjectFriendName = item.ProjectFriendName;
                        bug.BugType = item.BugType;
                        bug.CreatedBy = item.CreatedBy;
                        bug.ResolvedBy = item.ResolvedBy;
                        lstTrg.Add(bug);
                    }
                    ChartController.InsertAllBugs(lstTrg);

                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblBugList extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }

            #endregion

            #region refresh NumOfCreatedTC from Azure

            //truncate table NumOfCreatedTC
            ChartController.DeleteAllTblNumOfCreatedTC();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<NumOfCreatedTCAzure> query = NumOfCreatedTCAzureTable.Skip(skipCount).Take(takeCount);
                    List<NumOfCreatedTCAzure> lstTmp = await query.ToListAsync();

                    List<tblNumOfCreatedTC> lstTrg = new List<tblNumOfCreatedTC>();
                    foreach (NumOfCreatedTCAzure item in lstTmp)
                    {
                        tblNumOfCreatedTC tc = new tblNumOfCreatedTC();
                        tc.ID = item.Id;
                        tc.Application = item.Application;
                        tc.CreatedDate = item.CreatedDate;
                        tc.NumCreatedTC = item.NumCreatedTC;
                        lstTrg.Add(tc);
                    }

                    ChartController.InsertAllTblNumOfCreatedTC(lstTrg);


                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblNumOfCreatedTC extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh TestAutomation from Azure

            //truncate table TestAutomation
            ChartController.DeleteAllTblTestAutomation();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<TestAutomationAzure> query = TestAutomationAzureTable.Skip(skipCount).Take(takeCount);
                    List<TestAutomationAzure> lstTmp = await query.ToListAsync();

                    List<tblTestAutomation> lstTrg = new List<tblTestAutomation>();
                    foreach (TestAutomationAzure item in lstTmp)
                    {
                        tblTestAutomation t = new tblTestAutomation();
                        t.ID = item.Id;
                        t.Application = item.Application;
                        t.CreatedDate = item.CreatedDate;
                        t.TotalTestCases = item.TotalTestCases;
                        t.Automatable = item.Automatable;
                        t.CannotAutomated = item.CannotAutomated;
                        t.AutomatableNotReady = item.AutomatableNotReady;
                        t.AlreadyAutomated = item.AlreadyAutomated;
                        t.Gap = item.Gap;
                        t.RateAutomatable = item.RateAutomatable;
                        t.RateAlreadyAutomated = item.RateAlreadyAutomated;
                        t.LinesOfAutomationCode = item.LinesOfAutomationCode;
                        lstTrg.Add(t);
                    }
                    ChartController.InsertAllTblTestAutomation(lstTrg);

                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblTestAutomation extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh NumOfExecutedTC from Azure

            //truncate table NumOfExecutedTC
            ChartController.DeleteAlltblNumOfExecutedTC();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<NumOfExecutedTCAzure> query = NumOfExecutedTCAzureTable.Skip(skipCount).Take(takeCount);
                    List<NumOfExecutedTCAzure> lstTmp = await query.ToListAsync();

                    List<tblNumOfExecutedTC> lstTrg = new List<tblNumOfExecutedTC>();
                    foreach (NumOfExecutedTCAzure item in lstTmp)
                    {
                        tblNumOfExecutedTC t = new tblNumOfExecutedTC();
                        t.ID = item.Id;
                        t.Application = item.Application;
                        t.ExecutedStartDt = item.ExecutedStartDt;
                        t.NumExecutedTC = item.NumExecutedTC;
                        t.NumPassed = item.NumPassed;
                        t.NumFailed = item.NumFailed;
                        lstTrg.Add(t);
                    }

                    ChartController.InsertAlltblNumOfExecutedTC(lstTrg);
                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblNumOfExecutedTC extracted completed!  ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh ReleaseInfoAzure from Azure

            //truncate table tblReleaseInfo
            ChartController.DeleteAlltblReleaseInfo();

            try
            {

                int takeCount = 900;
                int skipCount = 0;

                while (1 == 1)
                {
                    IMobileServiceTableQuery<ReleaseInfoAzure> query = ReleaseInfoAzureTable.Skip(skipCount).Take(takeCount);
                    List<ReleaseInfoAzure> lstTmp = await query.ToListAsync();

                    List<tblReleaseInfo> lstTrg = new List<tblReleaseInfo>();
                    foreach (ReleaseInfoAzure item in lstTmp)
                    {
                        tblReleaseInfo t = new tblReleaseInfo();
                        t.ID = item.Id;
                        t.EBIProjectName = item.EBIProjectName;
                        t.YYYYMM = item.YYYYMM;
                        t.ReleaseCnt = item.ReleaseCnt;
                        t.ReleaseTimes = item.ReleaseTimes;
                        t.TestCaseCnt = item.TestCaseCnt;
                        t.ChangeSetCnt = item.ChangeSetCnt;
                        t.FixedBug = item.FixedBug;
                        t.ChangeRequest = item.ChangeRequest;
                        t.Feature = item.Feature;
                        t.Issue = item.Issue;
                        t.Task = item.Task;
                        t.UserStory = item.UserStory;
                        lstTrg.Add(t);
                    }
                    ChartController.InsertAlltblReleaseInfo(lstTrg);

                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblReleaseInfo extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh TestCaseInCIAzure from Azure

            //truncate table TestCaseInCIAzure
            ChartController.DeleteAlltblTestCaseInCI();

            try
            {

                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<TestCaseInCIAzure> query = TestCaseInCIAzureTable.Skip(skipCount).Take(takeCount);
                    List<TestCaseInCIAzure> lstTmp = await query.ToListAsync();

                    List<tblTestCaseInCI> lstTrg = new List<tblTestCaseInCI>();

                    foreach (TestCaseInCIAzure item in lstTmp)
                    {
                        tblTestCaseInCI t = new tblTestCaseInCI();
                        t.ID = item.Id;
                        t.EBIProjectName = item.EBIProjectName;
                        t.ReleasedTestCaseCnt = item.ReleasedTestCaseCnt;
                        t.TestCaseInCI = item.TestCaseInCI;
                        t.TestCase_Auto_CI = item.TestCase_Auto_CI;
                        t.TestCase_Manual_CI = item.TestCase_Manual_CI;
                        t.UTCase_CI = item.UTCase_CI;
                        lstTrg.Add(t);
                    }

                    ChartController.InsertAlltblTestCaseInCI(lstTrg);
                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblTestCaseInCI extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh tblGlobalTestCasePassRate from Azure

            //truncate table GlobalTestCasePassRateAzure
            ChartController.DeleteAlltblGolbalTestCasePassRate();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<GlobalTestCasePassRateAzure> query = GlobalTestCasePassRateAzureAzureTable.Skip(skipCount).Take(takeCount);
                    List<GlobalTestCasePassRateAzure> lstTmp = await query.ToListAsync();

                    List<tblGlobalTestCasePassRate> lstTrg = new List<tblGlobalTestCasePassRate>();
                    foreach (GlobalTestCasePassRateAzure item in lstTmp)
                    {
                        tblGlobalTestCasePassRate t = new tblGlobalTestCasePassRate();
                        t.ID = item.Id;
                        t.Financialyear = item.Financialyear;
                        t.ProjectName = item.ProjectName;
                        t.PlanName = item.PlanName;
                        t.Passed = item.Passed;
                        t.Failed = item.Failed;
                        t.NotExecuted = item.NotExecuted;
                        t.Total = item.Total;
                        t.PassPercent = item.PassPercent;
                        lstTrg.Add(t);
                    }
                    ChartController.InsertAlltblGolbalTestCasePassRate(lstTrg);

                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblGlobalTestCasePassRate extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh tblGlobalTestCaseAutomation from Azure

            //truncate table GlobalTestCaseAutomationAzure
            ChartController.DeleteAlltblGlobalTestCaseAutomation();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<GlobalTestCaseAutomationAzure> query = GlobalTestCaseAutomationAzureTable.Skip(skipCount).Take(takeCount);
                    List<GlobalTestCaseAutomationAzure> lstTmp = await query.ToListAsync();

                    List<tblGlobalTestCaseAutomation> lstTrg = new List<tblGlobalTestCaseAutomation>();
                    foreach (GlobalTestCaseAutomationAzure item in lstTmp)
                    {
                        tblGlobalTestCaseAutomation t = new tblGlobalTestCaseAutomation();
                        t.ID = item.Id;
                        t.Financialyear = item.Financialyear;
                        t.Project = item.Project;
                        t.Automated = item.Automated;
                        t.HaveAutomationValue = item.HaveAutomationValue;
                        t.NotAutomationValue = item.NotAutomationValue;
                        t.AutomatedOfTotal = item.AutomatedOfTotal;
                        t.AutomatedOfAutomatable = item.AutomatedOfAutomatable;
                        lstTrg.Add(t);
                    }

                    ChartController.InsertAlltblGlobalTestCaseAutomation(lstTrg);
                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblGlobalTestCaseAutomation extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            #region refresh tblGlobalDRE from Azure

            //truncate table GlobalDREAzure
            ChartController.DeleteAlltblGlobalDRE();

            try
            {
                int takeCount = 900;
                int skipCount = 0;
                while (1 == 1)
                {
                    IMobileServiceTableQuery<GlobalDREAzure> query = GlobalDREAzureAzureTable.Skip(skipCount).Take(takeCount);
                    List<GlobalDREAzure> lstTmp = await query.ToListAsync();

                    List<tblGlobalDRE> lstTrg = new List<tblGlobalDRE>();

                    foreach (GlobalDREAzure item in lstTmp)
                    {
                        tblGlobalDRE t = new tblGlobalDRE();
                        t.ID = item.Id;
                        t.Financialyear = item.Financialyear;
                        t.Project = item.Project;
                        t.SITBugs = item.SITBugs;
                        t.ProductionUATBUGS = item.ProductionUATBUGS;
                        t.OtherBugs = item.OtherBugs;
                        t.DRE = item.DRE;
                        lstTrg.Add(t);
                    }

                    ChartController.InsertAlltblGlobalDRE(lstTrg);

                    if (lstTmp.Count < takeCount)
                    {
                        break;
                    }

                    skipCount = skipCount + takeCount;
                }
                this.OutputTextBox.Text = string.Format("----tblGlobalDRE extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
            }
            catch (MobileServiceInvalidOperationException ex)
            {
                MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
            }
            #endregion

            sw.Stop();
            this.OutputTextBox.Text = string.Format("----All the data has been extracted completed! Duration: {0}(seconds) ---", (sw.ElapsedMilliseconds / 1000).ToString()) + Environment.NewLine + OutputTextBox.Text;
        }

        //private async void ExtractDataFromAzure()
        //{
        //    this.OutputTextBox.Text = "";
        //    Stopwatch sw = new Stopwatch();

        //    sw.Start();
        //    this.OutputTextBox.Text = "----Starting Extract Data, please wait...---" + Environment.NewLine + OutputTextBox.Text;
        //    DateTime localLastUpdateDT;
        //    DateTime lastUpdateDT;

        //    #region refresh BugList from Azure

        //    //truncate table tblBuglist
        //    ChartController.DeleteAllBugs();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;

        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<BugListAzure> query = bugListAzureTable.Skip(skipCount).Take(takeCount);
        //            List<BugListAzure> lstTmp = await query.ToListAsync();

        //            List<tblBugList> lstTrg = new List<tblBugList>();
        //            foreach (BugListAzure item in lstTmp)
        //            {
        //                tblBugList bug = new tblBugList();
        //                bug.ID = item.Id;
        //                bug.ItemID = item.ItemID;
        //                bug.ProjectName = item.ProjectName;
        //                bug.AssignedTo = item.AssignedTo;
        //                bug.State = item.State;
        //                bug.Severity = item.Severity;
        //                bug.FoundInEnvironment = item.FoundInEnvironment;
        //                bug.CreatedDate = item.CreatedDate;
        //                bug.ResolvedDate = item.ResolvedDate;
        //                bug.ClosedDate = item.ClosedDate;
        //                bug.ProjectFriendName = item.ProjectFriendName;
        //                bug.BugType = item.BugType;
        //                bug.CreatedBy = item.CreatedBy;
        //                bug.ResolvedBy = item.ResolvedBy;
        //                lstTrg.Add(bug);
        //            }
        //            ChartController.InsertAllBugs(lstTrg);

        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }
        //        this.OutputTextBox.Text = string.Format("----tblBugList extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }

        //    #endregion

        //    #region refresh NumOfCreatedTC from Azure

        //    //truncate table NumOfCreatedTC
        //    ChartController.DeleteAllTblNumOfCreatedTC();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<NumOfCreatedTCAzure> query = NumOfCreatedTCAzureTable.Skip(skipCount).Take(takeCount);
        //            List<NumOfCreatedTCAzure> lstTmp = await query.ToListAsync();

        //            List<tblNumOfCreatedTC> lstTrg = new List<tblNumOfCreatedTC>();
        //            foreach (NumOfCreatedTCAzure item in lstTmp)
        //            {
        //                tblNumOfCreatedTC tc = new tblNumOfCreatedTC();
        //                tc.ID = item.Id;
        //                tc.Application = item.Application;
        //                tc.CreatedDate = item.CreatedDate;
        //                tc.NumCreatedTC = item.NumCreatedTC;
        //                lstTrg.Add(tc);
        //            }

        //            ChartController.InsertAllTblNumOfCreatedTC(lstTrg);


        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblNumOfCreatedTC extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh TestAutomation from Azure

        //    //truncate table TestAutomation
        //    ChartController.DeleteAllTblTestAutomation();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<TestAutomationAzure> query = TestAutomationAzureTable.Skip(skipCount).Take(takeCount);
        //            List<TestAutomationAzure> lstTmp = await query.ToListAsync();

        //            List<tblTestAutomation> lstTrg = new List<tblTestAutomation>();
        //            foreach (TestAutomationAzure item in lstTmp)
        //            {
        //                tblTestAutomation t = new tblTestAutomation();
        //                t.ID = item.Id;
        //                t.Application = item.Application;
        //                t.CreatedDate = item.CreatedDate;
        //                t.TotalTestCases = item.TotalTestCases;
        //                t.Automatable = item.Automatable;
        //                t.CannotAutomated = item.CannotAutomated;
        //                t.AutomatableNotReady = item.AutomatableNotReady;
        //                t.AlreadyAutomated = item.AlreadyAutomated;
        //                t.Gap = item.Gap;
        //                t.RateAutomatable = item.RateAutomatable;
        //                t.RateAlreadyAutomated = item.RateAlreadyAutomated;
        //                t.LinesOfAutomationCode = item.LinesOfAutomationCode;
        //                lstTrg.Add(t);
        //            }
        //            ChartController.InsertAllTblTestAutomation(lstTrg);

        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }
        //        this.OutputTextBox.Text = string.Format("----tblTestAutomation extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh NumOfExecutedTC from Azure

        //    //truncate table NumOfExecutedTC
        //    ChartController.DeleteAlltblNumOfExecutedTC();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<NumOfExecutedTCAzure> query = NumOfExecutedTCAzureTable.Skip(skipCount).Take(takeCount);
        //            List<NumOfExecutedTCAzure> lstTmp = await query.ToListAsync();

        //            List<tblNumOfExecutedTC> lstTrg = new List<tblNumOfExecutedTC>();
        //            foreach (NumOfExecutedTCAzure item in lstTmp)
        //            {
        //                tblNumOfExecutedTC t = new tblNumOfExecutedTC();
        //                t.ID = item.Id;
        //                t.Application = item.Application;
        //                t.ExecutedStartDt = item.ExecutedStartDt;
        //                t.NumExecutedTC = item.NumExecutedTC;
        //                t.NumPassed = item.NumPassed;
        //                t.NumFailed = item.NumFailed;
        //                lstTrg.Add(t);
        //            }

        //            ChartController.InsertAlltblNumOfExecutedTC(lstTrg);
        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }
        //        this.OutputTextBox.Text = string.Format("----tblNumOfExecutedTC extracted completed!  ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh ReleaseInfoAzure from Azure

        //    try
        //    {
        //        //debug:
        //        //SQLiteConnection conn = DBHelper.getConnection();
        //        //conn.DeleteAll<tblETLHistory>();
        //        //conn.DeleteAll<tblReleaseInfo>();

        //        localLastUpdateDT = ETLHelper.GetLocalLastUpdateDT("tblReleaseInfo");
        //        lastUpdateDT = await ETLHelper.GetAzureLastUpdateDT("tblReleaseInfo", localLastUpdateDT);

        //        int totalRows = 0;

        //        while (lastUpdateDT.Year < 9000)
        //        {
        //            int takeCount = 900;
        //            int skipCount = 0;

        //            while (1 == 1)
        //            {
        //                IMobileServiceTableQuery<ReleaseInfoAzure> query = ReleaseInfoAzureTable.Where(x => x.LastUpdateDT == lastUpdateDT).Skip(skipCount).Take(takeCount);
        //                List<ReleaseInfoAzure> lstTmp = await query.ToListAsync();

        //                List<tblReleaseInfo> lstUpdate = new List<tblReleaseInfo>();
        //                List<tblReleaseInfo> lstInsert = new List<tblReleaseInfo>();

        //                for (int i = 0; i < lstTmp.Count; i++)
        //                {
        //                    string action = lstTmp[i].Action;
        //                    if (action.ToUpper() == "I")
        //                    {
        //                        lstInsert.Add(new tblReleaseInfo(
        //                            lstTmp[i].Id
        //                            , lstTmp[i].EBIProjectName
        //                            , lstTmp[i].YYYYMM
        //                            , lstTmp[i].ReleaseCnt
        //                            , lstTmp[i].ReleaseTimes
        //                            , lstTmp[i].TestCaseCnt
        //                            , lstTmp[i].ChangeSetCnt
        //                            , lstTmp[i].FixedBug
        //                            , lstTmp[i].ChangeRequest
        //                            , lstTmp[i].Feature
        //                            , lstTmp[i].Issue
        //                            , lstTmp[i].Task
        //                            , lstTmp[i].UserStory
        //                            ));
        //                    }
        //                    else if (action.ToUpper() == "U")
        //                    {
        //                        lstUpdate.Add(new tblReleaseInfo(
        //                            lstTmp[i].Id
        //                            , lstTmp[i].EBIProjectName
        //                            , lstTmp[i].YYYYMM
        //                            , lstTmp[i].ReleaseCnt
        //                            , lstTmp[i].ReleaseTimes
        //                            , lstTmp[i].TestCaseCnt
        //                            , lstTmp[i].ChangeSetCnt
        //                            , lstTmp[i].FixedBug
        //                            , lstTmp[i].ChangeRequest
        //                            , lstTmp[i].Feature
        //                            , lstTmp[i].Issue
        //                            , lstTmp[i].Task
        //                            , lstTmp[i].UserStory

        //                            ));
        //                    }
        //                    totalRows++;
        //                }

        //                ChartController.InsertAlltblReleaseInfo(lstInsert);
        //                ChartController.UpdateAlltblReleaseInfo(lstUpdate);

        //                ETLHelper.InsertLocalLastUpdateDT("tblReleaseInfo", lastUpdateDT);

        //                if (lstTmp.Count < takeCount)
        //                {
        //                    break;
        //                }

        //                skipCount = skipCount + takeCount;
        //            }

        //            localLastUpdateDT = ETLHelper.GetLocalLastUpdateDT("tblReleaseInfo");
        //            lastUpdateDT = await ETLHelper.GetAzureLastUpdateDT("tblReleaseInfo", localLastUpdateDT);
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblReleaseInfo extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh TestCaseInCIAzure from Azure

        //    //truncate table TestCaseInCIAzure
        //    //ChartController.DeleteAlltblTestCaseInCI();

        //    try
        //    {
        //        //debug:
        //        //SQLiteConnection conn = DBHelper.getConnection();
        //        //conn.DeleteAll<TestCaseInCIAzure>();
        //        //conn.DeleteAll<TestCaseInCIAzure>();

        //        localLastUpdateDT = ETLHelper.GetLocalLastUpdateDT("tblTestCaseInCI");
        //        lastUpdateDT = await ETLHelper.GetAzureLastUpdateDT("tblTestCaseInCI", localLastUpdateDT);

        //        int totalRows = 0;

        //        while (lastUpdateDT.Year < 9000)
        //        {

        //            int takeCount = 900;
        //            int skipCount = 0;
        //            while (1 == 1)
        //            {
        //                IMobileServiceTableQuery<TestCaseInCIAzure> query = TestCaseInCIAzureTable.Skip(skipCount).Take(takeCount);
        //                List<TestCaseInCIAzure> lstTmp = await query.ToListAsync();

        //                List<tblTestCaseInCI> lstUpdate = new List<tblTestCaseInCI>();
        //                List<tblTestCaseInCI> lstInsert = new List<tblTestCaseInCI>();

        //                for (int i = 0; i < lstTmp.Count; i++)
        //                {
        //                    string action = lstTmp[i].Action;
        //                    if (action.ToUpper() == "I")
        //                    {

        //                        lstInsert.Add(new tblTestCaseInCI(
        //                    lstTmp[i].Id
        //                    , lstTmp[i].EBIProjectName
        //                    , lstTmp[i].ReleasedTestCaseCnt
        //                    , lstTmp[i].TestCaseInCI
        //                    , lstTmp[i].TestCase_Auto_CI
        //                    , lstTmp[i].TestCase_Manual_CI
        //                    , lstTmp[i].UTCase_CI
        //                    ));
        //                    }
        //                    else if (action.ToUpper() == "U")
        //                    {
        //                        lstUpdate.Add(new tblTestCaseInCI(
        //                   lstTmp[i].Id
        //                   , lstTmp[i].EBIProjectName
        //                   , lstTmp[i].ReleasedTestCaseCnt
        //                   , lstTmp[i].TestCaseInCI
        //                   , lstTmp[i].TestCase_Auto_CI
        //                   , lstTmp[i].TestCase_Manual_CI
        //                   , lstTmp[i].UTCase_CI
        //                   ));
        //                    }
        //                    totalRows++;
        //                }

        //                ChartController.InsertAlltblTestCaseInCI(lstInsert);
        //                ChartController.UpdateAlltblTestCaseInCI(lstUpdate);

        //                ETLHelper.InsertLocalLastUpdateDT("tblTestCaseInCI", lastUpdateDT);

        //                if (lstTmp.Count < takeCount)
        //                {
        //                    break;
        //                }

        //                skipCount = skipCount + takeCount;
        //            }

        //            localLastUpdateDT = ETLHelper.GetLocalLastUpdateDT("tblTestCaseInCI");
        //            lastUpdateDT = await ETLHelper.GetAzureLastUpdateDT("tblTestCaseInCI", localLastUpdateDT);
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblTestCaseInCI extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh tblGlobalTestCasePassRate from Azure

        //    //truncate table GlobalTestCasePassRateAzure
        //    ChartController.DeleteAlltblGolbalTestCasePassRate();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<GlobalTestCasePassRateAzure> query = GlobalTestCasePassRateAzureAzureTable.Skip(skipCount).Take(takeCount);
        //            List<GlobalTestCasePassRateAzure> lstTmp = await query.ToListAsync();

        //            List<tblGlobalTestCasePassRate> lstTrg = new List<tblGlobalTestCasePassRate>();
        //            foreach (GlobalTestCasePassRateAzure item in lstTmp)
        //            {
        //                tblGlobalTestCasePassRate t = new tblGlobalTestCasePassRate();
        //                t.ID = item.Id;
        //                t.Financialyear = item.Financialyear;
        //                t.ProjectName = item.ProjectName;
        //                t.PlanName = item.PlanName;
        //                t.Passed = item.Passed;
        //                t.Failed = item.Failed;
        //                t.NotExecuted = item.NotExecuted;
        //                t.Total = item.Total;
        //                t.PassPercent = item.PassPercent;
        //                lstTrg.Add(t);
        //            }
        //            ChartController.InsertAlltblGolbalTestCasePassRate(lstTrg);

        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblGlobalTestCasePassRate extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh tblGlobalTestCaseAutomation from Azure

        //    //truncate table GlobalTestCaseAutomationAzure
        //    ChartController.DeleteAlltblGlobalTestCaseAutomation();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<GlobalTestCaseAutomationAzure> query = GlobalTestCaseAutomationAzureTable.Skip(skipCount).Take(takeCount);
        //            List<GlobalTestCaseAutomationAzure> lstTmp = await query.ToListAsync();

        //            List<tblGlobalTestCaseAutomation> lstTrg = new List<tblGlobalTestCaseAutomation>();
        //            foreach (GlobalTestCaseAutomationAzure item in lstTmp)
        //            {
        //                tblGlobalTestCaseAutomation t = new tblGlobalTestCaseAutomation();
        //                t.ID = item.Id;
        //                t.Financialyear = item.Financialyear;
        //                t.Project = item.Project;
        //                t.Automated = item.Automated;
        //                t.HaveAutomationValue = item.HaveAutomationValue;
        //                t.NotAutomationValue = item.NotAutomationValue;
        //                t.AutomatedOfTotal = item.AutomatedOfTotal;
        //                t.AutomatedOfAutomatable = item.AutomatedOfAutomatable;
        //                lstTrg.Add(t);
        //            }

        //            ChartController.InsertAlltblGlobalTestCaseAutomation(lstTrg);
        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblGlobalTestCaseAutomation extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    #region refresh tblGlobalDRE from Azure

        //    //truncate table GlobalDREAzure
        //    ChartController.DeleteAlltblGlobalDRE();

        //    try
        //    {
        //        int takeCount = 900;
        //        int skipCount = 0;
        //        while (1 == 1)
        //        {
        //            IMobileServiceTableQuery<GlobalDREAzure> query = GlobalDREAzureAzureTable.Skip(skipCount).Take(takeCount);
        //            List<GlobalDREAzure> lstTmp = await query.ToListAsync();

        //            List<tblGlobalDRE> lstTrg = new List<tblGlobalDRE>();

        //            foreach (GlobalDREAzure item in lstTmp)
        //            {
        //                tblGlobalDRE t = new tblGlobalDRE();
        //                t.ID = item.Id;
        //                t.Financialyear = item.Financialyear;
        //                t.Project = item.Project;
        //                t.SITBugs = item.SITBugs;
        //                t.ProductionUATBUGS = item.ProductionUATBUGS;
        //                t.OtherBugs = item.OtherBugs;
        //                t.DRE = item.DRE;
        //                lstTrg.Add(t);
        //            }

        //            ChartController.InsertAlltblGlobalDRE(lstTrg);

        //            if (lstTmp.Count < takeCount)
        //            {
        //                break;
        //            }

        //            skipCount = skipCount + takeCount;
        //        }

        //        this.OutputTextBox.Text = string.Format("----tblGlobalDRE extracted completed! ---") + Environment.NewLine + OutputTextBox.Text;
        //    }
        //    catch (MobileServiceInvalidOperationException ex)
        //    {
        //        MessageBox.Show(ex.Message, "Error loading items", MessageBoxButton.OK);
        //    }
        //    #endregion

        //    sw.Stop();
        //    this.OutputTextBox.Text = string.Format("----All the data has been extracted completed! Duration: {0}(seconds) ---", (sw.ElapsedMilliseconds / 1000).ToString()) + Environment.NewLine + OutputTextBox.Text;
        ////}

        private void RefreshButton_Click(object sender, RoutedEventArgs e)
        {
            #region Security TODO
            id = "";
            //psd = PasswordTextBox.Text;
            psd = "";

            if (chkSave.IsChecked == true)
            {
                if (userSettings.Contains(ConstantDefinition.USER_ALIAS))
                {
                    userSettings[ConstantDefinition.USER_ALIAS] = EmailTextBox.Text.ToString();
                }
                else
                {
                    userSettings.Add(ConstantDefinition.USER_ALIAS, EmailTextBox.Text.ToString());
                }


                if (userSettings.Contains(ConstantDefinition.USER_PASSWORD))
                {
                    userSettings[ConstantDefinition.USER_PASSWORD] = AccountPasswordBox.Password.ToString();
                }
                else
                {
                    userSettings.Add(ConstantDefinition.USER_PASSWORD, AccountPasswordBox.Password.ToString());
                }
                id = (string)userSettings[ConstantDefinition.USER_ALIAS];
                psd = (string)userSettings[ConstantDefinition.USER_PASSWORD];

            }
            else
            {
                id = this.EmailTextBox.Text.ToString();
                psd = this.AccountPasswordBox.Password.ToString();
            }
            #endregion

            ExtractDataFromAzure_used();

            #region retired method

            //id = "";
            ////psd = PasswordTextBox.Text;
            //psd = "";

            //if (chkSave.IsChecked == true)
            //{
            //    if (userSettings.Contains(ConstantDefinition.USER_ALIAS))
            //    {
            //        userSettings[ConstantDefinition.USER_ALIAS] = EmailTextBox.Text.ToString();
            //    }
            //    else
            //    {
            //        userSettings.Add(ConstantDefinition.USER_ALIAS, EmailTextBox.Text.ToString());
            //    }


            //    if (userSettings.Contains(ConstantDefinition.USER_PASSWORD))
            //    {
            //        userSettings[ConstantDefinition.USER_PASSWORD] = AccountPasswordBox.Password.ToString();
            //    }
            //    else
            //    {
            //        userSettings.Add(ConstantDefinition.USER_PASSWORD, AccountPasswordBox.Password.ToString());
            //    }
            //    id = (string)userSettings[ConstantDefinition.USER_ALIAS];
            //    psd = (string)userSettings[ConstantDefinition.USER_PASSWORD];

            //}
            //else
            //{
            //    id = this.EmailTextBox.Text.ToString();
            //    psd = this.AccountPasswordBox.Password.ToString();
            //}

            //string soap = "";
            //string soapResp = "";
            //string strStartLog = "";
            //string strEndLog = "";


            //this.busyIndicator.AnimationStyle = (AnimationStyle)(8);    
            //this.busyIndicator.Content = "Downloading...";     
            ////this.busyIndicator.IsRunning = true;    

            //string fileName = @"EWS\FindItem.xml";

            //StreamResourceInfo resourceInfo = Application.GetResourceStream(new Uri(fileName, UriKind.Relative));

            //using (StreamReader reader = new StreamReader(resourceInfo.Stream))
            //{
            //    soap = reader.ReadToEnd();
            //    reader.Close();
            //}

            //NetworkCredential nc = new NetworkCredential(id, psd);



            //HttpWebRequest req = (HttpWebRequest)WebRequest.Create(url);

            //req.Credentials = nc;

            //req.Accept = "text/xml";
            //req.Method = "POST";
            //req.ContentType = "text/xml;charset=\"utf-8\"";
            //req.Headers["Agent"] = "SOAPTestApp";

            //AsyncInfo pkg = new AsyncInfo();
            //pkg.OutputTextBox = OutputTextBox;
            //pkg.Req = req;
            //pkg.ReqData = soap;

            //req.BeginGetRequestStream(GetMailListSend, pkg);

            //strEndLog = "----Refresh Data Starting----";
            //OutputTextBox.Text = strEndLog;

            #endregion
        }

        #region "EWS"
        
        public class AsyncInfo
        {
            public HttpWebRequest Req;
            public HttpWebResponse Resp;
            public string ReqData;
            public string RespData;
            public TextBox OutputTextBox;

            public AsyncInfo()
            {
                Req = null;
                Resp = null;
                ReqData = "";
                RespData = "";
                
            }
        }

        private string id = "";
        private string psd = "";
        string url = "https://aps.mail.microsoft.com/ews/exchange.asmx";




        
        
        private void GetMailListSend(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            string soap = pkg.ReqData;

            byte[] data = Encoding.UTF8.GetBytes(soap);

            using (Stream reqStream = req.EndGetRequestStream(ar))
            {
                reqStream.Write(data, 0, data.Length);
                reqStream.Close();
            }

            req.BeginGetResponse(GetMailListDown, pkg);

            string strEndLog = "----Searching New Data...Starting---";
            this.Dispatcher.BeginInvoke(() =>
            {
                pkg.OutputTextBox.Text = strEndLog + Environment.NewLine + pkg.OutputTextBox.Text;
            });
        }

        private void GetMailListDown(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            HttpWebResponse resp = (HttpWebResponse)req.EndGetResponse(ar);

            string result = "";

            using (Stream s = resp.GetResponseStream())
            {
                StreamReader sr = new StreamReader(s);

                result = sr.ReadToEnd();

                sr.Close();
                sr.Dispose();

                s.Close();
                s.Dispose();
            }

            int ia = result.IndexOf("t:ItemId");

            if (ia >= 0)
            {
                ia = result.IndexOf("\"", ia);
                ia += 1;

                int ib = result.IndexOf("\"", ia);

                if (ib >= 0)
                {
                    result = result.Substring(ia, ib - ia);

                    string fileName = @"EWS\GetItem_Email.xml";
                    string soap = "";

                    StreamResourceInfo resourceInfo = Application.GetResourceStream(new Uri(fileName, UriKind.Relative));

                    using (StreamReader reader = new StreamReader(resourceInfo.Stream))
                    {
                        soap = reader.ReadToEnd();
                        reader.Close();
                    }

                    soap = soap.Replace("@@ItemId@@", result);

                    HttpWebRequest req2 = (HttpWebRequest)WebRequest.Create(url);

                    NetworkCredential nc = new NetworkCredential(id, psd);
                    req2.Credentials = nc;

                    req2.Accept = "text/xml";
                    req2.Method = "POST";
                    req2.ContentType = "text/xml;charset=\"utf-8\"";
                    req2.Headers["Agent"] = "SOAPTestApp";

                    AsyncInfo pkg2 = new AsyncInfo();
                    pkg2.OutputTextBox = pkg.OutputTextBox;
                    pkg2.Req = req2;
                    pkg2.ReqData = soap;

                    req2.BeginGetRequestStream(GetMailDetailSend, pkg2);
                }
            }
        }

        private void GetMailDetailSend(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            string soap = pkg.ReqData;

            byte[] data = Encoding.UTF8.GetBytes(soap);

            using (Stream reqStream = req.EndGetRequestStream(ar))
            {
                reqStream.Write(data, 0, data.Length);
                reqStream.Close();
            }

            req.BeginGetResponse(GetMailDetailDown, pkg);
        }

        private void GetMailDetailDown(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            HttpWebResponse resp = (HttpWebResponse)req.EndGetResponse(ar);

            string result = "";

            using (Stream s = resp.GetResponseStream())
            {
                StreamReader sr = new StreamReader(s);

                result = sr.ReadToEnd();

                sr.Close();
                sr.Dispose();

                s.Close();
                s.Dispose();
            }

            int ia = result.IndexOf("t:AttachmentId");

            if (ia >= 0)
            {
                ia = result.IndexOf("\"", ia);
                ia += 1;

                int ib = result.IndexOf("\"", ia);

                if (ib >= 0)
                {
                    result = result.Substring(ia, ib - ia);

                    string fileName = @"EWS\GetAttachment.xml";
                    string soap = "";

                    StreamResourceInfo resourceInfo = Application.GetResourceStream(new Uri(fileName, UriKind.Relative));

                    using (StreamReader reader = new StreamReader(resourceInfo.Stream))
                    {
                        soap = reader.ReadToEnd();
                        reader.Close();
                    }

                    soap = soap.Replace("@@AttachmentId@@", result);

                    HttpWebRequest req2 = (HttpWebRequest)WebRequest.Create(url);

                    NetworkCredential nc = new NetworkCredential(id, psd);
                    req2.Credentials = nc;

                    req2.Accept = "text/xml";
                    req2.Method = "POST";
                    req2.ContentType = "text/xml;charset=\"utf-8\"";
                    req2.Headers["Agent"] = "SOAPTestApp";

                    AsyncInfo pkg2 = new AsyncInfo();
                    pkg2.OutputTextBox = pkg.OutputTextBox;
                    pkg2.Req = req2;
                    pkg2.ReqData = soap;

                    req2.BeginGetRequestStream(GetAttachmentSend, pkg2);

                    string strEndLog = "----Searching New Data...Successful---";
                    this.Dispatcher.BeginInvoke(() =>
                    {
                        pkg.OutputTextBox.Text = strEndLog + Environment.NewLine + pkg.OutputTextBox.Text;
                    });
                }
            }
        }

        private void GetAttachmentSend(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            string soap = pkg.ReqData;

            byte[] data = Encoding.UTF8.GetBytes(soap);

            using (Stream reqStream = req.EndGetRequestStream(ar))
            {
                reqStream.Write(data, 0, data.Length);
                reqStream.Close();
            }

            req.BeginGetResponse(GetAttachmentDown, pkg);

            string strEndLog = "----Downloading New Data...Starting---";
            this.Dispatcher.BeginInvoke(() =>
            {
                pkg.OutputTextBox.Text = strEndLog + Environment.NewLine + pkg.OutputTextBox.Text;
            });
        }

        private void GetAttachmentDown(IAsyncResult ar)
        {
            AsyncInfo pkg = (AsyncInfo)ar.AsyncState;

            HttpWebRequest req = pkg.Req;

            HttpWebResponse resp = (HttpWebResponse)req.EndGetResponse(ar);

            string result = "";

            using (Stream s = resp.GetResponseStream())
            {
                StreamReader sr = new StreamReader(s);

                result = sr.ReadToEnd();

                sr.Close();
                sr.Dispose();

                s.Close();
                s.Dispose();
            }

            int ia = result.IndexOf("<t:Content>");
            ia += 11;
            int ib = result.IndexOf("</t:Content>", ia);

            result = result.Substring(ia, ib - ia);

            string strEndLog = "----Downloading New Data...Successful---";
            this.Dispatcher.BeginInvoke(() =>
            {
                pkg.OutputTextBox.Text = strEndLog + Environment.NewLine + pkg.OutputTextBox.Text;
            });

             // Obtain the virtual store for the application.
            IsolatedStorageFile iso = IsolatedStorageFile.GetUserStoreForApplication();

            string fileName = string.Format("TestMatrix_{0}.sqlite", Guid.NewGuid().ToString());

            List<PhoneSetting> Settingdata = new List<PhoneSetting>();
            Settingdata.Add(new PhoneSetting() { DBFileName = fileName });

            // Write to the Isolated Storage
            XmlWriterSettings xmlWriterSettings = new XmlWriterSettings();
            xmlWriterSettings.Indent = true;

            using (IsolatedStorageFile myIsolatedStorage = IsolatedStorageFile.GetUserStoreForApplication())
            {
                using (IsolatedStorageFileStream stream = myIsolatedStorage.OpenFile(ConstantDefinition.DATA_REFRESH_SETTING_FILEN_AME, FileMode.Create))
                {
                    XmlSerializer serializer = new XmlSerializer(typeof(List<PhoneSetting>));
                    using (XmlWriter xmlWriter = XmlWriter.Create(stream, xmlWriterSettings))
                    {
                        serializer.Serialize(xmlWriter, Settingdata);
                    }
                }
            }

            using (IsolatedStorageFileStream output = iso.CreateFile(Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, fileName))))
            {
                byte[] data = Convert.FromBase64String(result);
                output.Write(data, 0, data.Length);
            }

            strEndLog = "----Refresh Data Completed----";
            this.Dispatcher.BeginInvoke(() =>
                {
                    pkg.OutputTextBox.Text = strEndLog + Environment.NewLine + pkg.OutputTextBox.Text;
                });

            if (userSettings.Contains(ConstantDefinition.LAST_REFRESHED_DATE))
            {
                userSettings[ConstantDefinition.LAST_REFRESHED_DATE] = DateTime.Now.ToString();
            }
            else
            {
                userSettings.Add(ConstantDefinition.LAST_REFRESHED_DATE, DateTime.Now.ToString());
            }
            
        }

        #endregion
    }
}